package cn.gson.crm.model.dao;

import java.sql.Date;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import cn.gson.crm.model.Jdbc;
import cn.gson.crm.model.entity.Staff;
import cn.gson.crm.model.entity.StaffVo;

public class StaffDao {
	/**
	 * 登录
	 * 
	 * @param name
	 * @param pwd
	 * @return
	 * @throws SQLException
	 */
	public Staff getStaffByNameAndPwd(String name, String pwd) throws SQLException {
		String sql = "select * from staff where staff_name=? and staff_password=? and satff_status=1";
		Map<String, Object> data = Jdbc.getInstace().executeQuery(sql, name, pwd);
		Staff s = null;
		if (data != null) {
			s = buid(data);
		}
		return s;
	}
	
	/**
	 * 查询咨询师分配跟进成功的人数
	 * 
	 * @param name
	 * @param pwd
	 * @return
	 * @throws SQLException
	 */
	public int getStaffCount(String id,String date) throws SQLException {
		StringBuilder sql=new StringBuilder();
		sql.append( "select count(*) count from allot where allot_staff_id=? and allot_student_id in"
				+ " (select total_student_id from total) ");
		Map<String, Object> data=null;
		if(date!=null&&!date.isEmpty()){
		List<Object> params = new ArrayList<>();
		buidDate(params, sql, date);
		 data= Jdbc.getInstace().executeQuery(sql.toString(),id, params.toArray());
		}else{
		 data= Jdbc.getInstace().executeQuery(sql.toString(),id);
		}
		Integer total = Integer.parseInt(data.get("count").toString());
		return (int) Math.ceil(total);
	}
	
	/**
	 * 查询咨询师协助成功的人数
	 * 
	 * @param name
	 * @param pwd
	 * @return
	 * @throws SQLException
	 */
	public int getStaffCoo(String id,String date) throws SQLException {
		StringBuilder sql=new StringBuilder();
		sql.append( "select count(*) count from cooperation where cooperation_staff_id=? and "
				+ " cooperation_student_id in"
				+ " (select total_student_id from total)");
		Map<String, Object> data=null;
		if(date!=null&&!date.isEmpty()){
		List<Object> params = new ArrayList<>();
		buidCooDate(params, sql, date);
		 data= Jdbc.getInstace().executeQuery(sql.toString(),id, params.toArray());
		}else{
		 data= Jdbc.getInstace().executeQuery(sql.toString(),id);
		}
		Integer total = Integer.parseInt(data.get("count").toString());
		return (int) Math.ceil(total);
	}
	/**
	 * 查询所有员工
	 * 
	 * @param name
	 * @param pwd
	 * @return
	 * @throws SQLException
	 */
	public List<Staff> getStaff() throws SQLException {
		String sql = "select * from staff ";
		List<Map<String, Object>> data = Jdbc.getInstace().executeQueryForList(sql);
		List<Staff>  staff=null;
		if(data!=null&&data.size()>0){
			staff=new ArrayList<Staff>();
			for (Map<String,Object> map : data) {
				staff.add(buid(map));
			}
		
		}
		return staff;
	}
	/**
	 * 根据名字查询员工
	 * 
	 * @param name
	 * @param pwd
	 * @return
	 * @throws SQLException
	 */
	public Staff getStaffByName(String name) throws SQLException {
		String sql = "select * from staff where staff_name=?";
		List<Map<String, Object>> data = Jdbc.getInstace().executeQueryForList(sql,name);
		Staff  staff=null;
		if(data!=null&&data.size()>0){
			staff=new Staff();
			for (Map<String,Object> map : data) {
				staff=(buid(map));
			}
		
		}
		return staff;
	}
	/**
	 * 查询所有员工
	 * 
	 * @param name
	 * @param pwd
	 * @return
	 * @throws SQLException
	 */
	public List<Map<String, Object>> getStaff(String statu, String date, String name, String phone, String address,
			String sex, String dept, String position) throws SQLException {
		StringBuilder sql = new StringBuilder();
		sql.append("select * from staff where 1=1 ");
		List<Object> params = new ArrayList<>();
		buildSql(params, sql, statu, date, name, phone, address, sex, dept, position);
		List<Map<String, Object>> data = Jdbc.getInstace().executeQueryForList(sql.toString(), params.toArray());
		return data;
	}

	/**
	 * 条件查询员工
	 * 
	 * @param name
	 * @param pwd
	 * @return
	 * @throws SQLException
	 */
	public List<Map<String, Object>> getStaffBy(int page, String statu, String date, String name, String phone,
			String address, String sex, String dept, String position) throws SQLException {
		StringBuilder sql = new StringBuilder();
		sql.append("select * from staff a inner join position b on a.staff_position_id=b.position_id "
				+ " inner join dept c on a.staff_dept_id=c.dept_id where 1=1 ");
		List<Object> params = new ArrayList<>();
		buildSql(params, sql, statu, date, name, phone, address, sex, dept, position);
		sql.append(" order by a.satff_status desc,a.staff_id desc limit ?,? ");
		int size = (page - 1) * pageSize;
		params.add(size);
		params.add(pageSize);
		List<Map<String, Object>> data = Jdbc.getInstace().executeQueryForList(sql.toString(), params.toArray());
		return data;
	}

	/**
	 * 根据id查询员工
	 * 
	 * @param name
	 * @param pwd
	 * @return
	 * @throws SQLException
	 */
	public Staff getStaffById(String id) throws SQLException {
		String sql = "select * from staff where staff_id=? ";
		Map<String, Object> data = Jdbc.getInstace().executeQuery(sql, id);
		Staff st = null;
		if (data != null) {
			st = buid(data);
		}
		return st;
	}

	/**
	 * 查询所有员工并分页
	 * 
	 * @param name
	 * @param pwd
	 * @return
	 * @throws SQLException
	 */
	private int pageSize = 4;

	public List<Map<String, Object>> getStaffLimit(int page) throws SQLException {
		String sql = "select * from staff a inner join position b on a.staff_position_id=b.position_id "
				+ " inner join dept c on a.staff_dept_id=c.dept_id order by a.satff_status desc,a.staff_id desc limit ?,? ";
		int size = (page - 1) * pageSize;
		List<Map<String, Object>> data = Jdbc.getInstace().executeQueryForList(sql, size, pageSize);
		return data;
	}

	/**
	 * 根据id查询
	 * 
	 * @param name
	 * @param pwd
	 * @return
	 * @throws SQLException
	 */
	public StaffVo getStaffByIds(String id) throws SQLException {
		String sql = "select a.satff_status satff_status,a.staff_address staff_address,a.staff_dept_id staff_dept_id,a.staff_email staff_email,a.staff_entrytime staff_entrytime,"
				+ " a.staff_id staff_id ,a.staff_name staff_name ,a.staff_phone  staff_phone ,a.staff_position_id staff_position_id,"
				+ " a.staff_sex staff_sex,b.position_name  position_name,c.dept_name dept_name from staff a inner join position b on a.staff_position_id=b.position_id "
				+ " inner join dept c on a.staff_dept_id=c.dept_id where staff_id=?";
		StaffVo s = null;
		Map<String, Object> data = Jdbc.getInstace().executeQuery(sql, id);
		if (data != null) {
			s = buids(data);
		}
		return s;
	}

	/**
	 * 条件查询总页数
	 * 
	 * @return
	 * @throws SQLException
	 */
	public int getTotal(String statu, String date, String name, String phone, String address, String sex, String dept,
			String position) throws SQLException {
		StringBuilder sql = new StringBuilder();
		sql.append("select count(*) total from staff where 1=1 ");
		List<Object> params = new ArrayList<>();
		buildSql(params, sql, statu, date, name, phone, address, sex, dept, position);
		Map<String, Object> map = Jdbc.getInstace().executeQuery(sql.toString(), params.toArray());
		Integer total = Integer.parseInt(map.get("total").toString());
		return (int) Math.ceil((double) total / pageSize);
	}

	/**
	 * 总页数
	 * 
	 * @return
	 * @throws SQLException
	 */
	public int getTotal() throws SQLException {
		String sql = "select count(*) total from staff";
		Map<String, Object> map = Jdbc.getInstace().executeQuery(sql);
		Integer total = Integer.parseInt(map.get("total").toString());
		return (int) Math.ceil((double) total / pageSize);
	}
	
	/**
	 * 总页数
	 * 
	 * @return
	 * @throws SQLException
	 */
	public int getAllotTotal() throws SQLException {
		String sql = "select count(*) total from staff where staff_position_id = 4";
		Map<String, Object> map = Jdbc.getInstace().executeQuery(sql);
		Integer total = Integer.parseInt(map.get("total").toString());
		return (int) Math.ceil((double) total / 9);
	}

	/**
	 * 新增员工
	 * 
	 * @param s
	 * @return
	 * @throws SQLException
	 */
	public boolean insertStaff(Staff s) throws SQLException {
		String sql = "insert into Staff(staff_name,staff_sex,staff_phone,staff_email,staff_address,staff_position_id,staff_dept_id,staff_password) value(?,?,?,?,?,?,?,?)";
		System.out.println(sql);
		return Jdbc.getInstace().executeUpdate(sql, s.getStaffName(), s.getStaffSex(), s.getStaffPhone(),
				s.getStaffEmail(), s.getStaffAddress(), s.getStaffPositionId(), s.getStaffDeptId(),
				s.getStaffPassword());
	}

	/**
	 * 删除员工,修改在职状态
	 * 
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public boolean deleteStaff(String id) throws SQLException {
		String sql = "update staff set satff_status=0 where staff_id=?";
		return Jdbc.getInstace().executeUpdate(sql, id);
	}

	/**
	 * 修改员工信息
	 * 
	 * @param
	 * @return
	 * @throws SQLException
	 */
	public boolean updateStaffById(Staff s) throws SQLException {
		String sql = "update staff set staff_name=?,staff_sex=?,staff_phone=?,staff_email=?,staff_address=?,staff_position_id=?,staff_dept_id=? where  staff_id=?";
		return Jdbc.getInstace().executeUpdate(sql, s.getStaffName(), s.getStaffSex(), s.getStaffPhone(),
				s.getStaffEmail(), s.getStaffAddress(), s.getStaffPositionId(), s.getStaffDeptId(), s.getStaffId());
	}

	/**
	 * 绑定参数
	 * 
	 * @param data
	 * @return
	 */
	private Staff buid(Map<String, Object> data) {
		Staff s = new Staff();
		if (data.get("staff_dept_id") != null) {
			s.setStaffDeptId(Integer.parseInt( data.get("staff_dept_id").toString()));
		}
		if (data.get("satff_status") != null) {
			s.setSatffStatus(Integer.parseInt( data.get("satff_status").toString()));
		}
		if (data.get("staff_address") != null) {
			s.setStaffAddress(data.get("staff_address").toString());
		}
		if (data.get("staff_email") != null) {
			s.setStaffEmail(data.get("staff_email").toString());
		}
		if (data.get("staff_entrytime") != null) {

			SimpleDateFormat sf=new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss");
			try {
				java.util.Date  d=sf.parse(data.get("staff_entrytime").toString());
				Date  date=new Date(d.getTime());

				s.setStaffEntrytime(date);
			} catch (ParseException e) {
				e.printStackTrace();
			}
			
		
		}
		if (data.get("staff_id") != null) {
			s.setStaffId(Integer.parseInt(data.get("staff_id").toString()));
		}
		if (data.get("staff_name") != null) {
			s.setStaffName(data.get("staff_name").toString());
		}
		if (data.get("staff_password") != null) {
			s.setStaffPassword(data.get("staff_password").toString());
		}
		if (data.get("staff_phone") != null) {
			s.setStaffPhone(data.get("staff_phone").toString());
		}
		if (data.get("staff_positionId") != null) {
			s.setStaffPositionId(Integer.parseInt(data.get("staff_positionId").toString()));
		}
		if (data.get("staff_sex") != null) {
			s.setStaffSex(Integer.parseInt(data.get("staff_sex").toString()));
		}

		return s;
	}

	/**
	 * 绑定StaffVo参数
	 * 
	 * @param data
	 * @return
	 */
	private StaffVo buids(Map<String, Object> data) {
		StaffVo s = new StaffVo();
		if (data.get("staff_dept_id") != null) {
			s.setStaffDeptId(Integer.parseInt(data.get("staff_dept_id").toString()));
		}
		if (data.get("satff_status") != null) {
			s.setSatffStatus(Integer.parseInt(data.get("satff_status").toString()));
		}
		if (data.get("staff_address") != null) {
			s.setStaffAddress(data.get("staff_address").toString());
		}
		if (data.get("staff_email") != null) {
			s.setStaffEmail(data.get("staff_email").toString());
		}
		if (data.get("staff_entrytime") != null) {
			SimpleDateFormat f = new SimpleDateFormat("yyyy-MM-dd");
			f.format(data.get("staff_entrytime"));
			s.setStaffEntrytime(Date.valueOf(f.format(data.get("staff_entrytime"))));
		}
		if (data.get("staff_id") != null) {

			s.setStaffId(Integer.parseInt(data.get("staff_id").toString()));
		}
		if (data.get("staff_name") != null) {

			s.setStaffName(data.get("staff_name").toString());
		}
		if (data.get("staff_password") != null) {
			s.setStaffPassword(data.get("staff_password").toString());
		}
		if (data.get("staff_phone") != null) {
			s.setStaffPhone(data.get("staff_phone").toString());
		}
		if (data.get("staff_position_id") != null) {
			s.setStaffPositionId(Integer.parseInt(data.get("staff_position_id").toString()));
		}
		if (data.get("staff_sex") != null) {
			s.setStaffSex(Integer.parseInt(data.get("staff_sex").toString()));
		}
		if (data.get("position_name") != null) {
			s.setPositionName(data.get("position_name").toString());
		}
		if (data.get("dept_name") != null) {
			s.setDeptName(data.get("dept_name").toString());
		}
		return s;
	}

	/**
	 * 条件绑定
	 * 
	 * @param params
	 * @param sql
	 * @param type
	 * @param userName
	 * @param dept
	 * @param tel
	 * @return
	 */
	private StringBuilder buildSql(List<Object> params, StringBuilder sql, String statu, String date, String name,
			String phone, String address, String sex, String dept, String position) {
		if (statu != null && !statu.isEmpty()) {
			sql.append(" and satff_status=?");
			params.add(statu);
		}
		if (date != null && !date.isEmpty()) {
			sql.append(" and staff_entrytime like ? ");
			params.add("%" + date + "%");
		}
		if (name != null && !name.isEmpty()) {
			sql.append(" and staff_name like ? ");
			params.add("%" + name + "%");
		}
		if (phone != null && !phone.isEmpty()) {
			sql.append(" and staff_phone like ?");
			params.add("%" + phone + "%");
		}
		if (address != null && !address.isEmpty()) {
			sql.append(" and staff_address like ? ");
			params.add("%" + address + "%");
		}
		if (sex != null && !sex.isEmpty()) {
			sql.append(" and staff_sex= ?");
			params.add(sex);
		}
		if (position != null && !position.isEmpty()) {
			sql.append(" and staff_position_id=?");
			params.add(position);
		}
		if (dept != null && !dept.isEmpty()) {
			sql.append(" and staff_dept_id=?");
			params.add(dept);
		}
		return sql;
	}
	
	/**
	 * allot绑定时间
	 */
	private StringBuilder buidDate(List<Object> params,StringBuilder sql,String date){
		if (date != null && !date.isEmpty()) {
			sql.append(" and allot_time like ? ");
			params.add("%" + date + "%");
		}
		return sql;
	}
	
	/**
	 * 协助绑定时间
	 */
	private StringBuilder buidCooDate(List<Object> params,StringBuilder sql,String date){
		if (date != null && !date.isEmpty()) {
			sql.append(" and cooperation_time like ? ");
			params.add("%" + date + "%");
		}
		return sql;
	}
}
